from app.models.models import DetectionResult, ResultGroup, ResultItem
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.future import select
from sqlalchemy.orm import selectinload

async def create_detection(db: AsyncSession, detection: dict):
    db_detection = DetectionResult(**detection)
    db.add(db_detection)
    await db.commit()
    await db.refresh(db_detection)
    return db_detection

async def get_result_groups_by_user(db: AsyncSession, user_id: int):
    result = await db.execute(select(ResultGroup).where(ResultGroup.user_id == user_id))
    return result.scalars().all()

async def get_result_group_detail(db: AsyncSession, group_id: int):
    result = await db.execute(
        select(ResultGroup).options(selectinload(ResultGroup.items)).where(ResultGroup.id == group_id)
    )
    group = result.scalar_one_or_none()
    return group

async def create_result_group_with_items(db: AsyncSession, user_id: int, title: str, items: list):
    group = ResultGroup(user_id=user_id, title=title)
    db.add(group)
    await db.flush()  # 获取group.id
    db_items = [ResultItem(group_id=group.id, oss_url=item["oss_url"], detection_info=item["detection_info"]) for item in items]
    db.add_all(db_items)
    await db.commit()
    await db.refresh(group)
    return group

async def get_result_group_by_user_and_title(db: AsyncSession, user_id: int, title: str):
    result = await db.execute(select(ResultGroup).where(ResultGroup.user_id == user_id, ResultGroup.title == title))
    return result.scalar_one_or_none() 